/*
 * University 
 */
package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.UniversityBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class University {

	private static Logger log = Logger.getLogger(University.class.getName());
	private static Database db = Database.getInstance();

	public static void create(UniversityBean ub) {
		try {
			Connection conn = db.getConnection();
			String query = "insert into `universities`" +
					"(`name`, `address`, `available`)values(?,?,?)";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, ub.getName());
			st.setString(2, ub.getAddress());
			st.setBoolean(3, ub.isAvailable());

			st.execute();

			st.close();
			conn.close();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void update(UniversityBean ub) {
		try {
			Connection conn = db.getConnection();
			String query = "update `universities` set `name`=?, " +
					"`address`=?, `available`=? where `university_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, ub.getName());
			st.setString(2, ub.getAddress());
			st.setBoolean(3, ub.isAvailable());
			st.setInt(4, ub.getUniversityId());

			st.executeUpdate();

			st.close();
			conn.close();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static boolean exists( String name ) {
		try {
			Connection conn = db.getConnection();
			String query = "select count(*) from `universities` " +
					"where `name`=?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, name);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				if (rs.getInt(1) == 1) {
					return true;
				}
			}

			return false;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static UniversityBean fetch(int universityId) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `universities` where `university_id`=?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, universityId);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				UniversityBean ub = new UniversityBean();
				ub.fill(rs);
				return ub;
			}
			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static List<UniversityBean> fetchAll() {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `universities`";
			PreparedStatement st = conn.prepareStatement(query);

			ResultSet rs = st.executeQuery();
			List<UniversityBean> ret = new LinkedList<UniversityBean>();
			while (rs.next()) {
				UniversityBean ub = new UniversityBean();
				ub.fill(rs);
				ret.add(ub);
			}

			return ret;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void remove( int univId ) {
		try {
			Connection conn = db.getConnection();
			String query = "delete from `universities` where `university_id`=?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, univId);
			st.execute();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}
}	